import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class Test_JDBC {
    //查询
    public static void main3(String[] args) throws SQLException {

        //1.创建数据源对象
        DataSource dataSource =new MysqlDataSource();
        //给数据源对象 设置必要的属性
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/test1?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("111111");

        //2.和数据服务器建立网络连接
        dataSource.getConnection();
        Connection connection =dataSource.getConnection();

        //3.程序构造sql语句
        String sql ="select * from student";
        PreparedStatement statement = connection.prepareStatement(sql);

        //4.执行sql
        ResultSet resultSet=statement.executeQuery();

        //5.遍历结果集合
        while(resultSet.next()){
            //循环 针对一行数据进行处理
            System.out.println(resultSet.getInt("id"));
            System.out.println(resultSet.getString("name"));
        }

        //6.释放上述资源
        resultSet.close();
        statement.close();
        connection.close();
    }
    //用户自行插入数据
    public static void main2(String[] args) throws SQLException {
        //0.让用户输入id 和name
        Scanner scanner= new Scanner(System.in);
        System.out.println("请输入id: ");
        int id = scanner.nextInt();
        System.out.println("请输入name: ");
        String name =scanner.next();

        //1.创建数据源对象
        DataSource dataSource =new MysqlDataSource();
        //给数据源对象 设置必要的属性
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/test1?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("111111");

        //2.和数据服务器建立网络连接
        dataSource.getConnection();
        Connection connection =dataSource.getConnection();
        System.out.println(connection);

        //3.程序构造sql语句
        String sql ="insert into student values(?,?)";
        //把sql语句发送到服务器上,让服务器执行
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,id);//1说明替换第一个问号
        statement.setString(2,name);
        int n=statement.executeUpdate();
        System.out.println(n);//表示执行操作影响 的条数

        //5.释放上述资源
        statement.close();
        connection.close();
    }
    public static void main1(String[] args) throws SQLException {
        //1.创建数据源对象
        DataSource dataSource =new MysqlDataSource();


        //给数据源对象 设置必要的属性
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/test1?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("111111");

        //2.和数据服务器建立网络连接
        dataSource.getConnection();
        Connection connection =dataSource.getConnection();
        System.out.println(connection);

        //3.程序构造sql语句
        String sql ="insert into student values(1,'张三')";
        //把sql语句发送到服务器上,让服务器执行
        PreparedStatement statement = connection.prepareStatement(sql);
        int n=statement.executeUpdate();

        //5.释放上述资源
        statement.close();
        connection.close();
    }

    public static void main4(String[] args) throws SQLException {
        //创建数据源对象
        DataSource dataSource =new MysqlDataSource();
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/test1?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("111111");

        //2.建立连接 和数据库服务器建立网络连接
        dataSource.getConnection();
        Connection connection=dataSource.getConnection();

        //3.程序构造sql
        String sql="insert into student values(9,'张三')";
        PreparedStatement statement = connection.prepareStatement(sql);


        //将sql 发送到服务器上执行
        int n=statement.executeUpdate();
        System.out.println(n);

        //释放上述资源
        statement.close();
        connection.close();
    }

    public static void main5(String[] args) throws SQLException {
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/test1?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("111111");

        dataSource.getConnection();
        Connection connection=dataSource.getConnection();

        String sql="select * from student";
        PreparedStatement statement =connection.prepareStatement(sql);

        ResultSet resultSet= statement.executeQuery();
        while(resultSet.next()){
            System.out.println(resultSet.getInt("id"));
            System.out.println(resultSet.getString("name"));
        }

        resultSet.close();
        statement.close();
        connection.close();
    }

}
